/*==============================================================*/
/* Database name:  ycsite                                       */
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2013/5/31 16:14:39                           */
/*==============================================================*/


--drop database ycsite
--go

/*==============================================================*/
/* Database: ycsite                                             */
/*==============================================================*/
create database ycsite
go

use ycsite
go

/*==============================================================*/
/* User: dbo                                                    */
/*==============================================================*/
--execute sp_grantdbaccess dbo
--go

/*==============================================================*/
/* Table: SysArea                                               */
/*==============================================================*/
create table SysArea (
   ID                   int                  identity,
   ProvinceSysNo        int                  null,
   CitySysNo            int                  null,
   ProvinceName         nvarchar(50)         null,
   CityName             nvarchar(50)         null,
   DistrictName         nvarchar(50)         null,
   Display              bit                  not null
)
on "PRIMARY"
go

alter table SysArea
   add constraint PK_U_Area primary key (ID)
      on "PRIMARY"
go

/*==============================================================*/
/* Table: SysClient                                             */
/*==============================================================*/
create table SysClient (
   ID                   bigint               identity,
   SysNo                nvarchar(20)         null,
   Name                 nvarchar(20)         null,
   Gender               nvarchar(20)         null,
   Birthday             datetime             null,
   Ethnic               nvarchar(20)         null,
   ProvinceSysNo        int                  null,
   CitySysNo            int                  null,
   Telphone             nvarchar(100)        null,
   LastWork             nvarchar(100)        null,
   Description          nvarchar(500)        null,
   BodyWeight           nvarchar(20)         null,
   Height               nvarchar(20)         null,
   IdNo                 nvarchar(20)         null,
   IdAddr               nvarchar(100)        null,
   Address              nvarchar(100)        null,
   Marriage             nvarchar(50)         null,
   Vision               nvarchar(50)         null,
   Education            nvarchar(50)         null,
   Professional         nvarchar(50)         null,
   Salery               nvarchar(50)         null,
   Overtime             nvarchar(20)         null,
   Workplace            nvarchar(50)         null,
   WayOfWorking         nvarchar(20)         null,
   WayOfRelaxing        nvarchar(20)         null,
   WorkStatue           nvarchar(50)         null,
   WorkExpress          nvarchar(50)         null,
   Graduate             nvarchar(50)         null,
   Email                nvarchar(100)        null,
   TypeOfWorking        nvarchar(100)        null,
   Certifcate           nvarchar(100)        null,
   EnglishLvl           nvarchar(50)         null,
   ComputerLvl          nvarchar(50)         null,
   ClientLvl            int                  not null default 0,
   Blocked              bit                  not null default 0,
   CreateDate           datetime             null default getdate()
)
on "PRIMARY"
go

alter table SysClient
   add constraint PK_U_Client primary key (ID)
      on "PRIMARY"
go

/*==============================================================*/
/* Table: SysClientBlockHistory                                 */
/*==============================================================*/
create table SysClientBlockHistory (
   ID                   bigint               identity,
   ClientID             bigint               null,
   Description          nvarchar(200)        null,
   CreateDate           datetime             not null,
   CreateBy             nvarchar(20)         null
)
go

alter table SysClientBlockHistory
   add constraint PK_SYSCLIENTBLOCKHISTORY primary key (ID)
go

/*==============================================================*/
/* Table: SysClientTraining                                     */
/*==============================================================*/
create table SysClientTraining (
   ID                   bigint               identity,
   CID                  bigint               null,
   YearFrom             int                  null,
   MonthFrom            int                  null,
   YearTo               int                  null,
   MonthTo              int                  null,
   Caption              nvarchar(50)         null,
   Title                nvarchar(50)         null,
   Description          nvarchar(500)        null
)
on "PRIMARY"
go

alter table SysClientTraining
   add constraint PK_U_TrainingExpress primary key (ID)
      on "PRIMARY"
go

/*==============================================================*/
/* Table: SysClientWorking                                      */
/*==============================================================*/
create table SysClientWorking (
   ID                   bigint               identity,
   CID                  bigint               null,
   YearFrom             int                  null,
   MonthFrom            int                  null,
   YearTo               int                  null,
   MonthTo              int                  null,
   Caption              nvarchar(50)         null,
   Title                nvarchar(50)         null,
   Description          nvarchar(500)        null
)
on "PRIMARY"
go

alter table SysClientWorking
   add constraint PK_U_WorkExpress primary key (ID)
      on "PRIMARY"
go

/*==============================================================*/
/* Table: SysCompany                                            */
/*==============================================================*/
create table SysCompany (
   ID                   bigint               identity,
   SysNo                nvarchar(20)         null,
   Caption              nvarchar(50)         null,
   Attribution          nvarchar(50)         null,
   Address              nvarchar(100)        null,
   Web                  nvarchar(100)        null,
   Telephone            nvarchar(20)         null,
   Description          nvarchar(500)        null,
   Service              nvarchar(200)        null,
   Structure            nvarchar(50)         null,
   Employees            nvarchar(50)         null,
   Nature               nvarchar(50)         null,
   CreateDate           datetime             null default getdate()
)
on "PRIMARY"
go

alter table SysCompany
   add constraint PK_U_Company primary key (ID)
      on "PRIMARY"
go

/*==============================================================*/
/* Table: SysCompanyContact                                     */
/*==============================================================*/
create table SysCompanyContact (
   ID                   bigint               identity,
   CID                  bigint               null,
   Contact              nvarchar(20)         null,
   Telephone            nvarchar(50)         null
)
on "PRIMARY"
go

alter table SysCompanyContact
   add constraint PK_SYSCOMPANYCONTACT primary key (ID)
      on "PRIMARY"
go

/*==============================================================*/
/* Table: SysCompanyEmployee                                    */
/*==============================================================*/
create table SysCompanyEmployee (
   ID                   bigint               identity,
   CID                  bigint               null,
   Caption              nvarchar(50)         null,
   MaxMen               nvarchar(50)         null,
   HouseholdRequirements nvarchar(50)         null,
   Gender               nvarchar(50)         null,
   AgeFrom              nvarchar(20)         null,
   AgeTo                nvarchar(20)         null,
   Education            nvarchar(50)         null,
   Salery               nvarchar(20)         null,
   SaleryType           nvarchar(50)         null,
   TrailSalery          nvarchar(50)         null,
   TrailMonth           nvarchar(20)         null,
   WorkingHours         nvarchar(50)         null,
   WayOfWorking         nvarchar(20)         null,
   WayOfRelaxing        nvarchar(20)         null,
   JobRequirements      nvarchar(500)        null,
   SalaryDescription    nvarchar(500)        null,
   Workplace            nvarchar(50)         null,
   Description          nvarchar(500)        null,
   PublishDate          datetime             null
)
on "PRIMARY"
go

alter table SysCompanyEmployee
   add constraint PK_U_CompanyEmployee primary key (ID)
      on "PRIMARY"
go

/*==============================================================*/
/* Table: SysDictionary                                         */
/*==============================================================*/
create table SysDictionary (
   ID                   bigint               identity,
   Category             nvarchar(50)         null,
   Caption              nvarchar(50)         null,
   Worth                nvarchar(50)         null,
   Sort                 int                  not null
)
on "PRIMARY"
go

alter table SysDictionary
   add constraint PK_SYSDICTIONARY primary key (ID)
      on "PRIMARY"
go

/*==============================================================*/
/* Table: SysIntroduce                                          */
/*==============================================================*/
create table SysIntroduce (
   ID                   int                  identity,
   CompanySysNo         nvarchar(20)         null,
   CompanyCaption       nvarchar(50)         null,
   CompanyAddress       nvarchar(100)        null,
   CompanyContact       nvarchar(20)         null,
   CompanyMobile        nvarchar(50)         null,
   CompanyEmployeeSysNo int                  null,
   InterviewDate        datetime             null,
   ClientSysNo          nvarchar(20)         null,
   ClientName           nvarchar(20)         null,
   ClientSex            nvarchar(20)         null,
   ClientBirthday       datetime             null,
   ClientBirthplace     nvarchar(50)         null,
   ClientPhone          nvarchar(100)        null,
   ClientEducation      nvarchar(50)         null,
   ClientWork           nvarchar(100)        null,
   ClientWorkExpress    nvarchar(50)         null,
   CreateDate           datetime             null default getdate()
)
on "PRIMARY"
go

alter table SysIntroduce
   add constraint PK_U_Introduce primary key (ID)
      on "PRIMARY"
go

/*==============================================================*/
/* Table: SysUser                                               */
/*==============================================================*/
create table SysUser (
   ID                   bigint               identity,
   LoginName            nvarchar(20)         null,
   NickName             nvarchar(20)         null,
   Password             nvarchar(40)         null,
   Available            bit                  not null
)
on "PRIMARY"
go

alter table SysUser
   add constraint PK_U_Operators primary key (ID)
      on "PRIMARY"
go

alter table SysClientBlockHistory
   add constraint FK_Reference_5 foreign key (ClientID)
      references SysClient (ID)
go

alter table SysClientTraining
   add constraint FK_SysClient_SysClientTraining foreign key (CID)
      references SysClient (ID)
go

alter table SysClientWorking
   add constraint FK_SysClient_SysClientWorking foreign key (CID)
      references SysClient (ID)
go

alter table SysCompanyContact
   add constraint FK_SysCompany_SysCompanyContact foreign key (CID)
      references SysCompany (ID)
go

alter table SysCompanyEmployee
   add constraint FK_SysCompany_SysCompanyEmployee foreign key (CID)
      references SysCompany (ID)
go

